"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : Compatibility
Case Name   : 包裹符前后含其他字符,部分测试点合理报错
Create At   : 2023/06/20
Owner       : c00603284
Description :
        1.建表
        2.包裹符前含其他字符
        3.包裹符后含其他字符
        4.有起始包裹符，无结束包裹符
        5.无起始包裹符，有结束包裹符
        6.包裹符内含转义符,包裹符与转义符不同
Expect      :
        1.创建成功
        2.成功
        3.成功
        4.合理报错
        5.成功
        6.成功
History     :
    2023/8/22 n@ningyali load data语法相关issue修复后，预期结果与实际结果不一致，
    经与开发沟通及与mysql结果对比，修改预期
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class LoadData0042(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.common = Common()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0042'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.file_path = os.path.join('/home', self.pri_user.ssh_user)

        text1 = '----pre_step1：创建数据文件；expect:创建成功----'
        self.log.info(text1)
        self.data1 = "测试aaa,dfs'ac*ac*ac';测试bbb,1231'bc&bc&bc';" \
                     "测试ccc,发发发'c,c,c,cc';测试ddd,'dcdcdc';"
        self.data2 = "测试aaa,'ac*ac*ac'第三方;测试bbb,'bc&bc&bc'feraf;" \
                     "测试ccc,'c,c,c,c,c,c'123;测试ddd,dcdcdc;"
        self.data3 = "测试aaa,'ac*ac*ac';测试bbb,'bc&bc&bc;" \
                     "测试ccc,'c,c,c,c,c,c;测试ddd,dcdcdc;"
        self.data4 = "测试aaa,'ac*ac*ac';测试bbb,bc&bc&bc';" \
                     "测试ccc,c,c,c,c,c,c';测试ddd,dcdcdc;"
        self.data5 = "测试aaa,'ac*ac*ac';测试bbb,'bc*bc*bc';" \
                     "测试ccc,'c*c*c*cc,c';测试ddd,dc*dc****dc;"
        res = self.load_data.create_data_file(self.pri_user, self.case_lable,
                                              self.data1, self.data2,
                                              self.data3, self.data4,
                                              self.data5)
        self.assertTrue(res, '执行失败:' + text1)

        text2 = '----pre_step2：创建兼容B库；expect:创建成功----'
        self.log.info(text2)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text2)

    def test_load(self):
        text = '----step1：建表；expect：建表成功----'
        self.log.info(text)
        cols = 'c_01 text,c_02 varchar'
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name, cols)
        self.assertTrue(res, '执行失败:' + text)

        text = "----step2：包裹符前含其他字符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_1.txt')
        res1 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        truncate=False, into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res1)
        expect = [['aaa', "dfs'ac*ac*ac'"], ['bbb', "1231'bc&bc&bc'"],
                  ['ccc', "发发发'c"], ['ddd', 'dcdcdc']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step3：包裹符后含其他字符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_2.txt')
        res2 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res2)
        expect = [['aaa',
                   "'ac*ac*ac'第三方;测试bbb,'bc&bc&bc'feraf;"
                   "测试ccc,'c,c,c,c,c,c'123;测试ddd,dcdcdc;+"],
                  ['', '']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step4：有起始包裹符，无结束包裹符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_3.txt')
        res3 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res3)
        expect = [['aaa', 'ac*ac*ac'],
                  ['bbb', "'bc&bc&bc;测试ccc,'c,c,c,c,c,c;测试ddd,dcdcdc;+"],
                  ['', '']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step5：无起始包裹符，有结束包裹符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_4.txt')
        res4 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res4)
        expect = [['aaa', 'ac*ac*ac'], ['bbb', "bc&bc&bc'"], ['ccc', 'c'],
                  ['ddd', 'dcdcdc']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step6：包裹符内含转义符,包裹符与转义符不同；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_5.txt')
        res5 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        into_type='', escaped_by='*',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res5)
        expect = [['aaa', 'acacac'], ['bbb', 'bc\\x08c\\x08c'],
                  ['ccc', 'ccccc,c'], ['ddd', 'dcdc**dc']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        self.log.info('----step7：清理环境；expect：清理成功----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       os.path.join(self.file_path,
                                                    f"{self.case_lable}*"))
        self.assertTrue(res, '执行失败:清理环境')
        self.log.info(f'----{os.path.basename(__file__)} end----')
